package cn.com.chec.en.controller.admin;

import cn.com.chec.en.controller.scheduler.DumpMysqlDataScheduler;
import cn.com.chec.en.dao.DBInfoDao;
import cn.com.chec.en.model.domain.SqlFileInfo;
import cn.com.chec.en.model.domain.TableDesc;
import cn.com.chec.en.util.PageBean;
import cn.com.chec.en.util.PropertiesUtil;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.io.FilenameUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

@Controller
@RequestMapping({ "/admin/dbTable" })
public class DBTableAdminController {
	protected static final Logger logger = Logger
			.getLogger(DBTableAdminController.class);

	@Resource
	private DBInfoDao dbInfoDao;

	@RequestMapping({ "/listTable" })
	public ModelAndView listTable(HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		return new ModelAndView("admin/sysProperties/listTable");
	}

	@RequestMapping({ "/descTable" })
	public ModelAndView descTable(HttpServletRequest request, ModelMap map)
			throws Exception {
		ModelAndView mav = new ModelAndView();
		String message = "";
		String viewName = "admin/sysProperties/descTable";
		String tblName = request.getParameter("tblName");
		tblName = StringUtils.isBlank(tblName) ? "" : tblName;
		List<String> tblNames = this.dbInfoDao.getAllTables();

		if ((StringUtils.isBlank(tblName)) || (!tblNames.contains(tblName))) {
			message = "数据表不存在!";
			viewName = "admin/common/message";
			map.put("message", message);
			mav.setViewName(viewName);
			mav.addAllObjects(map);
			return mav;
		}

		List<TableDesc> tableDescs = this.dbInfoDao.getTableDesc(tblName);

		map.put("message", message);
		map.put("tableDescs", tableDescs);
		map.put("tblName", tblName);
		mav.setViewName(viewName);
		mav.addAllObjects(map);
		return mav;
	}

	@RequestMapping({ "/queryTable" })
	public ModelAndView queryTable(HttpServletRequest request, ModelMap map)
			throws Exception {
		ModelAndView mav = new ModelAndView();
		String message = "";
		String viewName = "admin/sysProperties/queryTable";

		String tblName = request.getParameter("tblName");
		tblName = StringUtils.isBlank(tblName) ? "" : tblName;
		String sql = request.getParameter("sql");
		sql = StringUtils.isBlank(sql) ? "" : sql;
		List<String> tblNames = this.dbInfoDao.getAllTables();

		if ((StringUtils.isBlank(sql))
				|| (!sql.toLowerCase().startsWith("select"))
				|| (!tblNames.contains(tblName.toLowerCase()))) {
			message = "当前只允许查询操作!";
			viewName = "admin/common/message";
			map.put("message", message);
			mav.setViewName(viewName);
			mav.addAllObjects(map);
			return mav;
		}
		sql = sql.trim();

		map.put("sql", sql);
		map.put("tblName", tblName);
		mav.setViewName(viewName);
		mav.addAllObjects(map);
		return mav;
	}

	@SuppressWarnings("deprecation")
	@RequestMapping({ "/dumpTable" })
	public ModelAndView dumpTable(HttpServletRequest request, ModelMap map)
			throws Exception {
		ModelAndView mav = new ModelAndView("admin/common/message");
		String message = "";
		String mysqlDumpDir = request.getRealPath("/")
				+ "WEB-INF/pages/admin/sysProperties/sqlDump/";
		String flag = request.getParameter("flag");
		String tblNames = StringUtils.isNotBlank(request
				.getParameter("tblNames")) ? request.getParameter("tblNames")
				: "";

		if ((StringUtils.equals(flag, "1"))
				&& (StringUtils.isNotBlank(tblNames))) {
			tblNames = tblNames.replaceAll(",", " ");
		} else if (StringUtils.equals(flag, "2")) {
			List<String> tblNameList = this.dbInfoDao.getAllTables();
			tblNames = "";
			for (String tblName : tblNameList)
				tblNames = tblNames + " " + tblName;
		} else {
			map.put("message", "无效操作!");
			return mav;
		}
		if (this.dbInfoDao.dataDump(PropertiesUtil.getValue("mysqlBasePath")
				+ "bin/", PropertiesUtil.getValue("mysqlDBName"), tblNames,
				mysqlDumpDir))
			message = "备份完毕!&nbsp;&nbsp;<a href=\"" + request.getContextPath()
					+ "/admin/dbTable/exportList\">查看</a>";
		else {
			message = "备份失败!";
		}

		map.put("message", message);
		mav.addAllObjects(map);
		return mav;
	}

	@SuppressWarnings("deprecation")
	@RequestMapping({ "/exportList" })
	public ModelAndView exportList(HttpServletRequest request, ModelMap map)
			throws Exception {
		ModelAndView mav = new ModelAndView("admin/sysProperties/sqlList");
		request.setCharacterEncoding("UTF-8");
		int curPage = StringUtils.isNotBlank(request.getParameter("curPage")) ? Integer
				.parseInt(request.getParameter("curPage")) : 1;
		PageBean pageBean = new PageBean();
		pageBean.setCurPage(curPage);
		String mysqlDumpDir = request.getRealPath("/")
				+ "WEB-INF/pages/admin/sysProperties/sqlDump/";
		List<File> list = this.dbInfoDao.getSubDumpList(mysqlDumpDir,
				pageBean.getStart(), pageBean.getRowsPerPage());
		int count = this.dbInfoDao.getCountDumpList(mysqlDumpDir);
		pageBean.setMaxRowCount(count);
		pageBean.setMaxPage();
		pageBean.setPageNoList();

		List<SqlFileInfo> sqlFileInfoList = new ArrayList<SqlFileInfo>();
		SqlFileInfo fileInfo = null;
		for (File f : list) {
			fileInfo = new SqlFileInfo();
			fileInfo.setFileName(f.getName());
			fileInfo.setFileSize(f.length());
			fileInfo.setLastModified(DateFormatUtils.format(
					new Date(f.lastModified()), "yyyy-MM-dd HH:mm:ss"));

			sqlFileInfoList.add(fileInfo);
		}

		map.put("sqlFileInfoList", sqlFileInfoList);
		map.put("pageBean", pageBean);
		mav.addAllObjects(map);

		return mav;
	}

	@SuppressWarnings("deprecation")
	@RequestMapping({ "/exportData" })
	public ModelAndView exportData(HttpServletRequest request, ModelMap map)
			throws Exception {
		ModelAndView mav = new ModelAndView("admin/common/message");
		String message = "";

		String mysqlDumpDir = request.getRealPath("/")
				+ "WEB-INF/pages/admin/sysProperties/sqlDump/";
		String file = request.getParameter("sqlFile");
		String sqlFile = mysqlDumpDir + file;
		if ((StringUtils.isBlank(sqlFile)) || (!new File(sqlFile).exists())) {
			message = "抱歉SQL文件不存在,已经被删除!";
			map.put("message", message);
			mav.addAllObjects(map);
			return mav;
		}
		if (this.dbInfoDao.dataImport(PropertiesUtil.getValue("mysqlBasePath")
				+ "bin/", PropertiesUtil.getValue("mysqlDBName"), sqlFile))
			message = "数据导入完毕!&nbsp;&nbsp;<a href=\""
					+ request.getContextPath()
					+ "/admin/dbTable/listTable\">查看</a>";
		else {
			message = "数据导入失败!";
		}
		map.put("message", message);
		mav.addAllObjects(map);
		return mav;
	}

	@SuppressWarnings("deprecation")
	@RequestMapping({ "/dataLoad" })
	public ModelAndView dataLoad(HttpServletRequest request,
			HttpServletResponse response, ModelMap map) throws Exception {
		String message = "";
		String mysqlDumpDir = request.getRealPath("/")
				+ "WEB-INF/pages/admin/sysProperties/sqlDump/";
		String file = request.getParameter("sqlFile");
		String sqlFile = mysqlDumpDir + file;

		if ((StringUtils.isBlank(sqlFile)) || (!new File(sqlFile).exists())) {
			message = "SQL已经删除!&nbsp;&nbsp;<a href=\""
					+ request.getContextPath()
					+ "/admin/dbTable/listTable\">查看</a>";
			map.put("message", message);
			return new ModelAndView("admin/common/message", map);
		}

		File loadFile = new File(sqlFile);
		if (loadFile.isFile()) {
			String fileName = FilenameUtils.getBaseName(sqlFile);
			response.setContentType("application/x-download");
			response.setHeader("Location", fileName);
			response.setHeader("Cache-Control", "max-age=1000");
			response.setHeader("Content-Disposition", "attachment; filename="
					+ fileName + ".sql");
			OutputStream outputStream = response.getOutputStream();
			InputStream inputStream = new FileInputStream(loadFile);
			byte[] buffer = new byte[1024];
			int i = 0;
			while ((i = inputStream.read(buffer)) != -1) {
				outputStream.write(buffer, 0, i);
			}
			outputStream.flush();
			outputStream.close();
			inputStream.close();
			return null;
		}
		return null;
	}

	@SuppressWarnings("deprecation")
	@RequestMapping({ "/dataDelete" })
	public ModelAndView dataDelete(HttpServletRequest request, ModelMap map)
			throws Exception {
		String message = "";
		String mysqlDumpDir = request.getRealPath("/")
				+ "WEB-INF/pages/admin/sysProperties/sqlDump/";
		String file = request.getParameter("sqlFile");
		String sqlFile = mysqlDumpDir + file;

		if ((StringUtils.isBlank(sqlFile)) || (!new File(sqlFile).exists())) {
			message = "SQL已经删除!&nbsp;&nbsp;<a href=\""
					+ request.getContextPath()
					+ "/admin/dbTable/exportList\">查看</a>";
			map.put("message", message);
			return new ModelAndView("admin/common/message", map);
		}

		File loadFile = new File(sqlFile);
		loadFile.delete();
		message = "SQL已经删除!&nbsp;&nbsp;<a href=\"" + request.getContextPath()
				+ "/admin/dbTable/exportList\">查看</a>";
		map.put("message", message);
		return new ModelAndView("admin/common/message", map);
	}

	@SuppressWarnings("deprecation")
	@RequestMapping({ "/dataDeleteAll" })
	public ModelAndView dataDeleteAll(HttpServletRequest request, ModelMap map)
			throws Exception {
		String message = "";
		String mysqlDumpDir = request.getRealPath("/")
				+ "WEB-INF/pages/admin/sysProperties/sqlDump/";
		String file = request.getParameter("sqlFile");

		for (String s : file.split(",")) {
			s = mysqlDumpDir + s;
			File loadFile = new File(s);
			if (loadFile.exists()) {
				loadFile.delete();
			}
		}
		message = "SQL已经删除!&nbsp;&nbsp;<a href=\"" + request.getContextPath()
				+ "/admin/dbTable/exportList\">查看</a>";
		map.put("message", message);
		return new ModelAndView("admin/common/message", map);
	}

	@RequestMapping({ "/schedulerPage" })
	public ModelAndView schedulerPage() throws Exception {
		return new ModelAndView("admin/sysProperties/dbScheduler");
	}

	@SuppressWarnings("deprecation")
	@RequestMapping({ "/setScheduler" })
	public ModelAndView setScheduler(HttpServletRequest request, ModelMap map)
			throws Exception {
		String message = "";
		String hour = request.getParameter("hour");
		String min = request.getParameter("min");
		String sec = request.getParameter("sec");
		try {
			String configPath = request.getRealPath("/")
					+ "WEB-INF/pages/admin/sysProperties/schdulerDB";
			BufferedWriter bw = new BufferedWriter(new FileWriter(configPath));
			String cronExpression = sec + " " + min + " " + hour + " * * ?";
			bw.write(cronExpression);
			bw.close();
			message = "定时任务设置成功,任务会在每天<font color='red'>" + hour + "时" + min
					+ "分" + sec + "秒执行</font>";

			DBInfoDao dao = new DBInfoDao();
			List<String> tblNameList = dao.getAllTables();
			String tblNames = "";
			for (String tblName : tblNameList) {
				tblNames = tblNames + " " + tblName;
			}

			DumpMysqlDataScheduler sched = new DumpMysqlDataScheduler();
			sched.removeJob();
			String mysqlDumpDir = request.getRealPath("/")
					+ "WEB-INF/pages/admin/sysProperties/sqlDump/";
			sched.startJob(cronExpression, tblNames, mysqlDumpDir);
		} catch (FileNotFoundException e) {
			message = "定时任务设置失败";
			e.printStackTrace();
		} catch (IOException e) {
			message = "定时任务设置失败";
			e.printStackTrace();
		}
		map.put("message", message);
		return new ModelAndView("admin/common/message", map);
	}
}